﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using TracNghiemTinHocDaiCuong.ThucThe;
using TracNghiemTinHocDaiCuong.Common;

namespace TracNghiemTinHocDaiCuong.DAO
{
    public class BaiLamDAO
    {
        public static BaiLam DoiTuong(IDataRecord read)
        {
            return new BaiLam
            {
                MaBL = Methods.ConvertNumber(read["MaBL"].ToString()),
                MaND = Methods.ConvertNumber(read["MaND"].ToString()),
                MaDT = Methods.ConvertNumber(read["MaDT"].ToString()),
                DapAnND = Methods.ConvertString(read["DapAnND"].ToString()),
                NgayLamBai = Methods.ConvertDateTime(read["NgayLamBai"].ToString()),
                Diem = Methods.ConvertNumber(read["Diem"].ToString())
            };
        }

        public static DataTable KetQuaNguoiDung(int maND)
        {
            string query = string.Format("SELECT TenDT, TenCD, Diem, NgayLamBai "+
                            "FROM BaiLam, DeThi, ChuDe " +
                            "WHERE BaiLam.MaDT = DeThi.MaDT " +
                            "AND DeThi.MaCD = ChuDe.MaCD " +
                            "AND MaND=@MaND ");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaND", maND)
            };
            DataTable danhSach = SqlHelper.ExecuteQuery(query, parameter);
            if ((danhSach == null) || (danhSach.Rows.Count == 0))
                return null;
            else
                return danhSach;
        }

        public static string LayBaiLam(int idMaBL, int idNguoiDung, int idDe)
        {
            string query = string.Format("SELECT DapAnND FROM BaiLam WHERE MaND=@MaND AND MaDT=@MaDT AND MaBL=@MaBL");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaND", idNguoiDung),
                new SqlParameter("@MaDT", idDe),
                new SqlParameter("@MaBL", idMaBL)
            };
            DataTable dt = SqlHelper.ExecuteQuery(query, parameter);
            DataRow row = dt.Rows[0];
            return row["DapAnND"].ToString();
        }

        public static IList<BaiLam> LayDanhSach()
        {
            IList<BaiLam> danhSach = new List<BaiLam>();
            string query = string.Format("SELECT * FROM BaiLam");
            IDataReader read = SqlHelper.ExecuteReader(query);
            while(read.Read())
            {
                danhSach.Add(DoiTuong(read));
            }
            return danhSach;
        }

        public static int Them(BaiLam baiLam)
        {
            string query = string.Format("INSERT INTO BaiLam(MaND, MaDT, DapAnND, NgayLamBai, Diem) " +
            "VALUES(@MaND, @MaDT, @DapAnND, @NgayLamBai, @Diem) SELECT @@IDENTITY AS MaBL");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaND", baiLam.MaND),
                new SqlParameter("@MaDT", baiLam.MaDT),
                new SqlParameter("@DapAnND", baiLam.DapAnND),
                new SqlParameter("@NgayLamBai", baiLam.NgayLamBai),
                new SqlParameter("@Diem", baiLam.Diem)
            };
            IDataReader readMaBL = SqlHelper.ExecuteReader(query, parameter);
            int maBL = 0;
            while (readMaBL.Read())
            {
                maBL = Methods.ConvertNumber(readMaBL["MaBL"].ToString());
            }
            return maBL;
        }

        public static bool Sua(BaiLam baiLam)
        {
            string query = string.Format("UPDATE BaiLam SET MaND=@MaND, MaDT=@MaDT, " +
            "DapAnND=@DapAnND WHERE MaBL=@MaBL");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaND", baiLam.MaND),
                new SqlParameter("@MaDT", baiLam.MaDT),
                new SqlParameter("@DapAnND", baiLam.DapAnND),
                new SqlParameter("@MaBL", baiLam.MaBL)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }

        public static bool Xoa(int idBaiLam)
        {
            string query = string.Format("DELETE FROM BaiLam WHERE MaBL=@MaBL");
            var parameter = new Collection<SqlParameter>
            {
                new SqlParameter("@MaBL", idBaiLam)
            };
            SqlHelper.ExecuteNonQuery(query, parameter);
            return true;
        }
    }
}